﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OracleClient;
using System.Configuration;

public class DataAccess
{
    private OracleConnection conn;
    private OracleCommand cmd;
    private OracleDataAdapter da;
    private DataSet ds;
    private DataTable dt;

    public DataAccess() { }

    public string GetConnectionString()
    {
        return ConfigurationManager.ConnectionStrings["SystemCMS_G2BConnectionStrings"].ToString();
    }

    // Declare var OracleConnection
    public OracleConnection OpenConnection()
    {
        conn = new OracleConnection(this.GetConnectionString());
        try
        {
            if (conn != null)
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            conn.Open();
        }
        catch (Exception)
        {
            return null;
        }
        return conn;
    }

    // Close connection
    public void CloseConnection(OracleConnection conn)
    {
        if (conn != null || conn.State == ConnectionState.Open)
        {
            conn.Close();
            conn.Dispose();
        }
    }

    //
    public object ExecuteScalar(string sqlQuery)
    {
        conn = OpenConnection();

        object result = null;
        cmd = new OracleCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = sqlQuery;

        try
        {
            result = cmd.ExecuteScalar();
            return result;
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            cmd.Dispose();
            CloseConnection(conn);
        }
    }


    public object ExecuteScalar(string spName, OracleParameter[] lstPara)
    {
        // Open Connection
        conn = OpenConnection();

        object result = null;
        cmd = new OracleCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = spName;
        cmd.Parameters.AddRange(lstPara);

        try
        {
            result = cmd.ExecuteScalar();
            return result;
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            cmd.Dispose();
            CloseConnection(conn);
        }
    }

    public int ExecuteNonQuery(string spName, OracleParameter[] lstPara)
    {
        // Open Connection
        conn = OpenConnection();

        int result = 0;
        cmd = new OracleCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = spName;
        cmd.Parameters.AddRange(lstPara);

        try
        {
            result = cmd.ExecuteNonQuery();
            return result;
        }
        catch (Exception)
        {
            return 0;
        }
        finally
        {
            cmd.Dispose();
            CloseConnection(conn);
        }
    }

    public DataTable ExecuteSelect(string spName)
    {
        conn = OpenConnection();

        cmd = new OracleCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = spName;

        //ds = new DataSet();
        da = new OracleDataAdapter()
        {
            SelectCommand = cmd
        };
        try
        {
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            cmd.Dispose();
            CloseConnection(conn);
        }
    }

    public DataTable ExecuteSelect(string spName, OracleParameter[] lstPara)
    {
        conn = OpenConnection();

        cmd = new OracleCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = spName;
        cmd.Parameters.AddRange(lstPara);

        da = new OracleDataAdapter(cmd);
        try
        {
            dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            cmd.Dispose();
            CloseConnection(conn);
        }
    }

    public DataSet ExecuteSelectFillDataSet(string spName, OracleParameter[] lstPara)
    {
        conn = OpenConnection();

        this.cmd = new OracleCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = spName;
        cmd.Parameters.AddRange(lstPara);

        ds = new DataSet();
        da = new OracleDataAdapter();
        try
        {
            da.Fill(ds);
            return ds;
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            cmd.Dispose();
            CloseConnection(conn);
        }
    }
}